﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SqlToLua
{
    class SQlReader
    {
        //异或因子
        private byte[] xorScale = new byte[] { 45, 66, 38, 55, 23, 254, 9, 165, 90, 19, 41, 45, 201, 58, 55, 37, 254, 185, 165, 169, 19, 171 };//.data文件的xor加解密因子

        string connStr = "server=118.89.29.234;user id=lotillDb; password=lotillDb; port=3306; database=p2_res; pooling=false; charset=utf8";

        string codePath = "E:\\CreateData\\";
        string dataPath = "E:\\CreateData\\";
        Action completeCallback;

        public SQlReader(Action callback)
        {
            completeCallback = callback;
        }

        public void Test()
        {
            string rowStr = "select COLUMN_COMMENT FROM information_schema.columns WHERE table_name = 'emoji_base'";
            DataSet data = MySqlHelper.ExecuteDataset(connStr, rowStr);
            foreach (DataRow mDr in data.Tables[0].Rows)
            {
                foreach (DataColumn mDc in data.Tables[0].Columns)
                {
                    Console.WriteLine(mDr[mDc].ToString());
                }
            }
        }

        public void SetPath(string _codePath, string _dataPath)
        {
            codePath = _codePath;
            dataPath = _dataPath;
        }

        public void Read()
        {
            Start("emoji_act");
            //Start("emoji_base");
            //Start("emoji_map");
            GetAllTableName();
        }

        public void Start(string tableName)
        {
            string fileName = TableName2FileName(tableName);
            Thread thread = new Thread(() => SaveSqlToFile(fileName, tableName));
            thread.Start();
            //SaveSqlToFile(fileName, tableName);
        }

        #region 获取所有表名
        public List<string> GetAllTableName()
        {
            List<string> tableList = new List<string>();
            string rowStr = "select table_name from information_schema.tables where table_schema='p2_res' and table_type='base table'";
            DataSet values = MySqlHelper.ExecuteDataset(connStr, rowStr);

            Console.WriteLine(values.Tables.Count);
            Console.WriteLine(values.Tables[0].Rows.Count);
            Console.WriteLine(values.Tables[0].Columns.Count);
            foreach (DataRow mDr in values.Tables[0].Rows)
            {
                foreach (DataColumn mDc in values.Tables[0].Columns)
                {
                    Console.WriteLine(mDr[mDc].ToString());
                    tableList.Add(mDr[mDc].ToString());
                }
            }

            return tableList;
        }
        #endregion

        #region 读取数据库并生成Lua文件 SaveSqlToFile
        private void SaveSqlToFile(string fileName, string tableName)
        {
            if (!Directory.Exists(codePath))
            {
                Directory.CreateDirectory(codePath);
            }
            if (!Directory.Exists(dataPath))
            {
                Directory.CreateDirectory(dataPath);
            }

            List<string> descArr = new List<string>();
            string rowStr = string.Format("select COLUMN_COMMENT FROM information_schema.columns WHERE table_name = '{0}'", tableName);
            DataSet data = MySqlHelper.ExecuteDataset(connStr, rowStr);
            foreach (DataRow mDr in data.Tables[0].Rows)
            {
                foreach (DataColumn mDc in data.Tables[0].Columns)
                {
                    //Console.WriteLine(mDr[mDc].ToString());
                    //descArr.Add(mDr[mDc].ToString());
                }
            }

            string valueStr = string.Format("select * from {0}", tableName);
            DataSet values = MySqlHelper.ExecuteDataset(connStr, valueStr);

            int rowLen = values.Tables[0].Rows.Count;
            int colLen = values.Tables[0].Columns.Count;

            Console.WriteLine("开始生成：" + fileName);

            //写入行数
            //写入列数
            Console.WriteLine(rowLen + "行数=======================");
            Console.WriteLine(colLen + "列数=======================");

            StringBuilder sbr = new StringBuilder();

            if (fileName.IndexOf("Config") == -1)
            {
                fileName = "Config" + fileName;
            }
            sbr.AppendFormat("module(\"{0}\", package.seeall)", fileName);
            sbr.Append("\r\n");
            sbr.Append("\r\n");
            sbr.AppendFormat("local configName = \"{0}\"", fileName);
            sbr.Append("\r\n");
            sbr.AppendFormat("local configTable = \r\n", fileName);
            sbr.Append("{");
            sbr.Append("\r\n");

            int index = 1;
            //写入值
            for (int i = 0; i < values.Tables[0].Rows.Count; i++)
            {
                for (int j = 0; j < values.Tables[0].Columns.Count; j++)
                {
                    if (j == 0)
                    {
                        if (i != 0)
                        {
                            sbr.Append("\r\n");
                            sbr.Append("    },");
                            sbr.Append("\r\n");
                        }
                        sbr.AppendFormat("    [{0}] = ", index);
                        sbr.Append("\r\n    {");
                    }
                    sbr.Append("\r\n");

                    if (values.Tables[0].Rows[i][values.Tables[0].Columns[j]].GetType() == typeof(string))
                    {
                        string temp = values.Tables[0].Rows[i][values.Tables[0].Columns[j]].ToString();
                        if (temp == null || temp == "")
                        {
                            sbr.AppendFormat("        [\"{0}\"] = \"\",", values.Tables[0].Columns[j].ToString());
                        }
                        else
                        {
                            sbr.AppendFormat("        [\"{0}\"] = \"{1}\",", values.Tables[0].Columns[j].ToString(), values.Tables[0].Rows[i][values.Tables[0].Columns[j]]);
                        }
                    }
                    else if (values.Tables[0].Rows[i][values.Tables[0].Columns[j]].GetType() == typeof(DateTime))
                    {
                        string temp = values.Tables[0].Rows[i][values.Tables[0].Columns[j]].ToString();
                        if (temp == null || temp == "")
                        {
                            sbr.AppendFormat("        [\"{0}\"] = nil,", values.Tables[0].Columns[j].ToString());
                        }
                        else
                        {
                            DateTime dt = (DateTime)(values.Tables[0].Rows[i][values.Tables[0].Columns[j]]);
                            sbr.AppendFormat("        [\"{0}\"] = \"{1}\",", values.Tables[0].Columns[j].ToString(), dt.ToString("yyyy-MM-dd HH:mm:ss"));
                        }
                    }
                    else
                    {
                        string temp = values.Tables[0].Rows[i][values.Tables[0].Columns[j]].ToString();
                        if (temp == null || temp == "")
                        {
                            sbr.AppendFormat("        [\"{0}\"] = nil,", values.Tables[0].Columns[j].ToString());
                        }
                        else
                        {
                            sbr.AppendFormat("        [\"{0}\"] = {1},", values.Tables[0].Columns[j].ToString(), values.Tables[0].Rows[i][values.Tables[0].Columns[j]].ToString());
                        }
                    }
                }
                index++;
            }

            sbr.Append("\r\n");
            sbr.Append("    }");
            sbr.Append("\r\n");
            sbr.Append("}");
            AppendCommonFunction(sbr, fileName);
            SpecialFunction.AppendFunction(sbr, fileName);

            using (FileStream fs = new FileStream(Path.Combine(codePath, string.Format("{0}.lua", fileName)), FileMode.Create))
            {
                using (StreamWriter sw = new StreamWriter(fs))
                {
                    sw.Write(sbr.ToString());
                }
            }

            Console.WriteLine(fileName + "生成完毕！");

            if (completeCallback != null)
            {
                completeCallback.Invoke();
            }
        }

        private void AppendCommonFunction(StringBuilder sb, string table)
        {
            sb.Append("\r\n\r\nfunction getVarByCustom(name, value)");
            sb.Append("\r\n    for key, var in pairs(configTable) do");
            sb.Append("\r\n        if var[name] == value then");
            sb.Append("\r\n            return var");
            sb.Append("\r\n        end");
            sb.Append("\r\n    end");
            sb.Append("\r\n    return nil");
            sb.Append("\r\nend");


            sb.Append("\r\n\r\nfunction getVarById(id)");
            sb.Append("\r\n    for key, var in pairs(configTable) do");
            sb.Append("\r\n        if tonumber(var.id) == tonumber(id) then");
            sb.Append("\r\n            return var");
            sb.Append("\r\n        end");
            sb.Append("\r\n    end");
            sb.Append("\r\n    return nil");
            sb.Append("\r\nend");

            sb.Append("\r\n\r\nfunction getVarByIndex(index)");
            sb.Append("\r\n    return configTable[tonumber(index)]");
            sb.Append("\r\nend");

            sb.Append("\r\n\r\nfunction getSize()");
            sb.Append("\r\n    return #configTable");
            sb.Append("\r\nend");
        }

        #endregion

        #region Type到相应字符串的转换
        private string ChangeTypeName(Type type)
        {
            if (type == typeof(int))
            {
                return "ReadInt()";
            }
            else if (type == typeof(string))
            {
                return "ReadUTF8()";
            }
            throw new Exception("未定义的类型");
        }

        private void WriteByteByType(MMO_MemoryStream ms, object data)
        {
            if (data.GetType() == typeof(int))
            {
                ms.WriteInt((int)data);
            }
            else if (data.GetType() == typeof(string))
            {
                ms.WriteUTF8String(data.ToString());
            }
        }

        private string GetTypeStringByType(Type type)
        {
            if (type == typeof(int))
            {
                return "int";
            }
            else if (type == typeof(string))
            {
                return "string";
            }
            throw new Exception("未定义的类型");
        }

#endregion

        #region 字符串转换的帮助方法
        private string CoverFirstWordUpper(string input)
        {
            string prev = input.Substring(0, 1);
            string next = input.Substring(1, input.Length - 1);
            string total = string.Format("{0}{1}", prev.ToUpper(), next);
            return total;
        }

        private string TableName2FileName(string tableName)
        {
            Regex reg = new Regex("_");
            Match m = reg.Match(tableName);
            List<string> elementList = new List<string>();
            int startIndex = 0;
            while (m.Success)
            {
                string temp = tableName.Substring(startIndex, m.Index - startIndex);
                string endTemp = CoverFirstWordUpper(temp);
                elementList.Add(endTemp);
                startIndex = m.Index + m.Length;

                m = m.NextMatch();
            }
            if (startIndex < tableName.Length)
            {
                string temp = tableName.Substring(startIndex);
                string endTemp = CoverFirstWordUpper(temp);
                elementList.Add(endTemp);
            }
            return string.Join("", elementList.ToArray());
        }
        #endregion
    }
}
